In D-ESC/ODMr: ODM Data Access

This is an R package for working with an instance of the CUAHSI ODM running on MS-SQL. Works in our office, may or may not be useful to others outside our work-site.

ODBC

In order to talk to the database, you'll need to put an ODBC connection on the machine that you're working on. To do this, do Control Panel -> Administrative Tools, then choose "Data Sources (ODBC)". You'll want to add a "User DSN".

At this point, it'll ask you which driver you want to use. You'll want to choose SQL Server and click finish. Next you'll want to specify which database you want to talk to. You'll see a dialogue box with three boxes for you to fill in.

Click next and choose that you want to connect with SQL Server authentication using a username and password given to you by the administrator.

Click "Next" again, and it'll contact the database to makesure that you have the appropriate permissions. Make sure that you have the “OD” database selected as the default database. Click next again, then finish, and finally, click "Test Data Source" to make sure that everything ran properly.

The Basics

This will all be easier to use and understand if you have a fundamental understanding of the underlying data model. The model and the underlying motives for it's design can be found at http://www.cuahsi.org

Here is a quick example. You'll need the package RODBC in order to establish a connection using an ODBC connection. RODBC::odbcConnect establishes a connection to the specified DSN and ODMgetcatalog() can be used to import the series catalog.

library(ODMr)
ODM <- odbcConnect("Connection", "User id", "Password")
catalog = ODMgetcatalog()

Using the established connection and referencing the series catalog we can import data from the database. The series catalog is not stable so only use this method to quickly look at certain data.

Data <- ODMselect(ODM, SeriesID = 10, startDate = "2013-06-01",
  endDate = "2014-06-01")

A more stable reference can be had by specifying SiteID, VariableID, MethodID and QualityControlLevelID.

Data <- ODMselect(ODM, SiteID = 1, VariableID = 1, MethodID = 9, QCLevelID = 0)

Multiple data series can be queried at once. The function makes use of the IN operator in the underlying SQL statement to specify multiple values.

tmp = ODMselect(ODM, SiteID = c(1,5) , VariableID = 1, MethodID = 9,
  QCLevelID = 0, startDate = "2013-06-01", endDate = "2013-07-01")

Addins

The are a number of addins included in the package. Once the package is installed they should be available under Addins in the toolbar of Rstudio.

• ODMconnect
• ODMgetDataValues
• ODMtools

ODMconnect provides a simple GUI to establish a connection to the database. Hopefully in the future we'll store connection info to make it easier to reconnect to the database.

ODMgetDataValues provides a simple GUI to access a download data from ODM. The addin will create and execute the code so that it can easily be saved and reused.

ODMtools provides an interactive way of viewing, validating, and/or correcting values.

D-ESC/ODMr documentation built on Sept. 16, 2021, 10:52 a.m.